﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ImportarBd
{
    class Program
    {
        public static OleDbConnection conectarAccess()
        {
            string solutionDirectory = ((EnvDTE.DTE)System.Runtime
                                              .InteropServices
                                              .Marshal
                                              .GetActiveObject("VisualStudio.DTE.10.0"))
                                   .Solution
                                   .FullName;
            solutionDirectory = System.IO.Path.GetDirectoryName(solutionDirectory) + @"\dacli\bd\DACLI.MDB";
            string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + solutionDirectory + ";Persist Security Info=False;";
            OleDbConnection MyConn = new OleDbConnection(ConnStr);
            return MyConn;
        }

        public static SqlConnection conectarSQLServer()
        {
            string ConnStr = @"Data Source=(local)\sqlExpress;Initial Catalog=dacli;Integrated Security=True;MultipleActiveResultSets=True";
            SqlConnection MyConn = new SqlConnection(ConnStr);            
            return MyConn;
        }

        static long examenCategoriaId(SqlCommand comandoSQL, string categoria)
        {
            long id = -1;
            comandoSQL.CommandText = "SELECT * FROM examenes_categoria WHERE nombre ='" + categoria + "'";
            SqlDataReader reader = comandoSQL.ExecuteReader();
            
            if (reader.Read())
                id = reader.GetInt64(reader.GetOrdinal("id"));
            
            reader.Close();
            return id;
        }
        static long examenId(SqlCommand comandoSQL, string examen)
        {
            long id = -1;
            comandoSQL.CommandText = "SELECT * FROM examenes WHERE nombre ='" + examen + "'";
            SqlDataReader reader = comandoSQL.ExecuteReader();

            if (reader.Read())
                id = reader.GetInt64(reader.GetOrdinal("id"));

            reader.Close();
            return id;
        }

        static int idenId(SqlCommand comandoSQL, string iden)
        {
            int id = -1;
            comandoSQL.CommandText = "SELECT * FROM iden WHERE nombre ='" + iden + "'";
            SqlDataReader reader = comandoSQL.ExecuteReader();

            if (reader.Read())
                id = reader.GetInt32(reader.GetOrdinal("id"));

            reader.Close();
            return id;
        }
        static int tipoPrecioId(SqlCommand comandoSQL, string iden)
        {
            int id = -1;
            comandoSQL.CommandText = "SELECT * FROM precios_tipo WHERE campo ='" + iden + "'";
            SqlDataReader reader = comandoSQL.ExecuteReader();

            if (reader.Read())
                id = reader.GetInt32(reader.GetOrdinal("id"));

            reader.Close();
            return id;
        }

        
        public static string getNombrePrecio(string precioDacli4)
        {
            string result = "";
            if (precioDacli4.ToLower() == "normal")
                result =  "precio_normal";
            if (precioDacli4.ToLower() == "bioanalista")
                result = "precio_bioanalista";
            if (precioDacli4.ToLower() == "emergencia")
                result = "precio_emergencia";
            if (precioDacli4.ToLower() == "empr1")
                result = "precio1";
            if (precioDacli4.ToLower() == "empr2")
                result = "precio2";
            if (precioDacli4.ToLower() == "empr3")
                result = "precio3";
            if (precioDacli4.ToLower() == "empr4")
                result = "precio4";
            if (precioDacli4.ToLower() == "empr5")
                result = "precio5";
            if (precioDacli4.ToLower() == "empr6")
                result = "precio6";
            if (precioDacli4.ToLower() == "empr7")
                result = "precio7";
            if (precioDacli4.ToLower() == "empr8")
                result = "precio8";
            if (precioDacli4.ToLower() == "empr9")
                result = "precio9";
            if (precioDacli4.ToLower() == "empr10")
                result = "precio10";
            if (precioDacli4.ToLower() == "empr11")
                result = "precio11";
            if (precioDacli4.ToLower() == "empr12")
                result = "precio12";
            if (precioDacli4.ToLower() == "empr13")
                result = "precio13";
            if (precioDacli4.ToLower() == "empr14")
                result = "precio14";
            if (precioDacli4.ToLower() == "empr15")
                result = "precio15";
            return result; 
        }
        static void Main(string[] args)
        {
            OleDbConnection connectionAccess = conectarAccess();
            SqlConnection connectionSQL = conectarSQLServer();
            connectionSQL.Open();
            SqlCommand comandoSQL = new SqlCommand("tets", connectionSQL);
            OleDbCommand comandoAccess = new OleDbCommand("Test", connectionAccess);

            #region Inicializacion
            comandoSQL.CommandText = "DELETE  FROM pedido_detalle; DBCC CHECKIDENT (pedido_detalle, reseed, 0)";
            comandoSQL.ExecuteNonQuery();
            comandoSQL.CommandText = "DELETE  FROM pagos;DBCC CHECKIDENT (pagos, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM resultados_categorias;DBCC CHECKIDENT (resultados_categorias, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM pedidos;DBCC CHECKIDENT (pedidos, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM facturas;DBCC CHECKIDENT (facturas, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM examenes; DBCC CHECKIDENT (examenes, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM opciones_elementos;DBCC CHECKIDENT (opciones_elementos, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM elementos_categoria; DBCC CHECKIDENT (elementos_categoria, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM examenes_categoria; DBCC CHECKIDENT (examenes_categoria, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM clientes; DBCC CHECKIDENT (clientes, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM iden_precio; DBCC CHECKIDENT (iden_precio, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            comandoSQL.CommandText = "DELETE  FROM iden; DBCC CHECKIDENT (iden, reseed, 0)";
            comandoSQL.ExecuteNonQuery();

            #endregion

            #region Examenes            
            connectionAccess.Open();
            comandoAccess.CommandText = "SELECT * FROM grupo";
            OleDbDataReader accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            { 
                string descripcion =accessReader.GetString(accessReader.GetOrdinal("Descripcion"));               
                comandoSQL.CommandText = "INSERT INTO examenes_categoria(Nombre,Filas) VALUES('" + descripcion + "',4)";
                comandoSQL.ExecuteNonQuery();
            }

            comandoAccess.CommandText = "SELECT Examenes.Descripcion as examen, Grupo.Descripcion as grupo FROM Examenes INNER JOIN Grupo ON Examenes.IdGrupo = Grupo.IdGrupo WHERE recepcion=true";
            accessReader.Close();
            accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            {
                string examen = accessReader.GetString(accessReader.GetOrdinal("examen"));
                string grupo = accessReader.GetString(accessReader.GetOrdinal("grupo"));
                long examenes_categoria_id = examenCategoriaId(comandoSQL, grupo);
                comandoSQL.CommandText = "INSERT INTO examenes(Nombre, examenes_categoria_id) VALUES('" + examen + "'," + examenes_categoria_id + ")";
                comandoSQL.ExecuteNonQuery();
            }
           
            #endregion
            
            #region Pacientes

            comandoAccess.CommandText = "SELECT *FROM Paciente";
            accessReader.Close();
            accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            {
                try
                {
                    string nombre = accessReader.GetString(accessReader.GetOrdinal("Nombres"));
                    string cedula = accessReader.GetString(accessReader.GetOrdinal("Cedula"));
                    string direccion = accessReader.GetString(accessReader.GetOrdinal("direccion"));
                    string telefono = accessReader.GetString(accessReader.GetOrdinal("Telefono"));
                    string sql = "INSERT INTO clientes(cedula,nombres,direccion,telefono) values ('" + cedula + "','" + nombre + "','" + direccion + "','" + telefono + "')";
                    comandoSQL.CommandText = sql;
                    comandoSQL.ExecuteNonQuery();
                }
                catch (Exception e)
                { 

                }


            }

            #endregion

            #region Precios

            comandoAccess.CommandText = "SELECT nombreprecio,monto, descripcion FROM precios INNER JOIN examenes ON precios.Cod_Examen = examenes.Cod_examen";
            accessReader.Close();
            accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            {
                string precio = getNombrePrecio(accessReader.GetString(accessReader.GetOrdinal("NombrePrecio")));
                string examen = accessReader.GetString(accessReader.GetOrdinal("descripcion"));
                decimal monto = accessReader.GetDecimal(accessReader.GetOrdinal("Monto"));
                long examen_id = examenId(comandoSQL, examen);
                comandoSQL.CommandText = "UPDATE examenes SET " + precio + "=" + monto + " WHERE id=" + examen_id;
                comandoSQL.ExecuteNonQuery();
            }

            comandoAccess.CommandText = "SELECT * FROM iden";
            accessReader.Close();
            accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            {
                string ide = accessReader.GetString(accessReader.GetOrdinal("IDE"));
                comandoSQL.CommandText = "INSERT INTO iden(nombre) VALUES('" + ide+ "')";
                comandoSQL.ExecuteNonQuery();
            }

            comandoAccess.CommandText = "SELECT * FROM relaciones";
            accessReader.Close();
            accessReader = comandoAccess.ExecuteReader();
            while (accessReader.Read())
            {
                string precio = getNombrePrecio(accessReader.GetString(accessReader.GetOrdinal("precio")));
                int id = idenId(comandoSQL,accessReader.GetString(accessReader.GetOrdinal("IDEN")));
                int tipoId = tipoPrecioId(comandoSQL, precio);
                comandoSQL.CommandText = "INSERT INTO iden_precio(iden_id, precios_tipo_id) VALUES(" + id + ","+ tipoId + ")";
                comandoSQL.ExecuteNonQuery();
            }
    
            #endregion
            Console.WriteLine("La importacion de datos a terminado, presione cualquier tecla para continuar...");
            Console.ReadKey();
        }
    }
}
    